// const Promise = require('bluebird');
// request = Promise.promisifyAll(request); 
const PoolJira=require('../Tools/Pool').PoolJira;
const TimeTool = require('../Tools/TimeTool');
const {jira,user} = require('../Configs');
let request = require('request');


class jiraDbModels {
    
    async JiraDbEachMonthBug(year,projectId) {
        
        if(!year || !projectId)
         throw  new Error('年份和项目ID为空')
        
        let monthStartAndEnd = TimeTool.getEachMonthDays(year)

        let EachArray = []

        for (let i = 0; i < monthStartAndEnd.length; i++) {
            let month = monthStartAndEnd[i].m
            let firstDay = monthStartAndEnd[i].firstDay
            let lastDay = monthStartAndEnd[i].lastDay
           // console.log('###', typeof firstDay, '~~~', lastDay, 'mmm', month)
            let sql = `SELECT
                        COUNT( * )as sunc 
                    FROM
                        jiraissue,
                        issuestatus,
                        project 

                    WHERE
                        jiraissue.PROJECT = project.ID 
                        AND jiraissue.issuestatus = issuestatus.ID 
                        AND PROJECT = '${projectId}' 
                        AND CREATED BETWEEN '${firstDay}' 
                        AND '${lastDay}'`;
            let res = await PoolJira.queryAsync(sql);
            EachArray.push({
                x: month + '月',
                y: res[0].sunc
            })
        }


       // console.log('ee', EachArray)

        return EachArray;
    }

    async JiraDbAllMonthBug(year) {
        
        if(!year )
         throw  new Error('年份为空')
        
        let monthStartAndEnd = TimeTool.getEachMonthDays(year)

        let EachArray = []

        for (let i = 0; i < monthStartAndEnd.length; i++) {
            let month = monthStartAndEnd[i].m
            let firstDay = monthStartAndEnd[i].firstDay
            let lastDay = monthStartAndEnd[i].lastDay
           // console.log('###', typeof firstDay, '~~~', lastDay, 'mmm', month)
            let sql = `SELECT
                        COUNT( * )as sunc 
                    FROM
                        jiraissue,
                        issuestatus,
                        project 

                    WHERE
                        jiraissue.PROJECT = project.ID 
                        AND jiraissue.issuestatus = issuestatus.ID 
                        AND CREATED BETWEEN '${firstDay}' 
                        AND '${lastDay}'`;
            let res = await PoolJira.queryAsync(sql);
            EachArray.push({
                x: month + '月',
                y: res[0].sunc
            })
        }


       // console.log('ee', EachArray)

        return EachArray;
    }
    
    //查询项目某一版本某一严重程度的bug数量
    //pname --项目名称, version --版本，severity --严重程度
    async JiraDbBugSeverity(pname,version,severity){
        let sql  = `SELECT COUNT(customvalue) as count
        FROM 
        (
        SELECT project.pname, jiraissue.id AS issueid, projectversion.vname,customfield.cfname,customfieldoption.customvalue
        FROM nodeassociation, jiraissue, project, projectversion,customfield ,customfieldoption,customfieldvalue
        WHERE ASSOCIATION_TYPE='IssueVersion' 
        AND jiraissue.PROJECT = project.ID
        AND jiraissue.id = nodeassociation.SOURCE_NODE_ID
        AND nodeassociation.SINK_NODE_ID = projectversion.ID
        
        
        AND  jiraissue.ID=customfieldvalue.ISSUE
        AND
        customfieldvalue.STRINGVALUE=customfieldoption.ID
        AND
        customfieldoption.CUSTOMFIELD=customfield.ID
        
        
        AND project.pname='${pname}'
        AND customfield.cfname="严重程度"
        AND  projectversion.vname='${version}'
        AND customvalue='${severity}'
        ) AS issueversion`
        let res = await PoolJira.queryAsync(sql);
        return res[0].count
    }

    //查询单项目挂起bug数
    async JiraDbHandingCount(pname){
        let sql = `SELECT 
            count(*) as count 
        FROM 
            jiraissue,
            project 
        WHERE 
            project.pname = '${pname}'
            AND jiraissue.PROJECT = project.id 
            AND issuestatus = 10003`
        let res = await PoolJira.queryAsync(sql);
        return res[0].count
    }

    //查询单项目bug总数
    async JiraDbTotalBug(pname){
        let sql = `SELECT 
            count(*) as count 
        FROM 
            jiraissue,
            project 
        WHERE 
            project.pname = '${pname}'
            AND jiraissue.PROJECT = project.id `        
        let res = await PoolJira.queryAsync(sql);
        return res[0].count
    }
    //查询jira的版本号
    async jiraDbVersionList(pname){
        let sql = `SELECT  v.vname as version 
            FROM 
                projectversion as v,project as p 
            WHERE 
                p.pname = '${pname}'
                AND  p.id = v.project
                ORDER BY v.id DESC
                LIMIT 0,5`
        
        let res = await PoolJira.queryAsync(sql);
        let versionlist = res.map((r)=>{
            return{
                vname:r.version
            }
        })
        return versionlist
    }
    //查询项目pkey
    async jiraDbPkey(pname ){
        let sql = `SELECT pkey FROM project WHERE pname = '${pname}'`
        let res = await PoolJira.queryAsync(sql)
        return res[0].pkey
    }
}



module.exports = new jiraDbModels();